/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package modele;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collection;
import java.util.HashSet;
import java.util.TreeSet;

public class DAO {

    public static Collection<Dep> getLesDeps() {
        Collection<Dep> lesDeps = new HashSet<Dep>();
        try {
            Connection con = Connect.get();
            Statement req;
            req = con.createStatement();

            //1e partie
            ResultSet rs = req.executeQuery("select distinct departement from medecin order by departement ASC;");
            while (rs.next()) {
                Collection<Med> lesMeds = new TreeSet<Med>();
                Statement req2;
                req2 = con.createStatement();
                ResultSet rs2 = req2.executeQuery("select * from medecin where departement=" + rs.getString("departement"));
                while (rs2.next()) {

                    String idmedecin;
                    String nommedecin;
                    String prenommedecin;
                    String adressemedecin;
                    String telmedecin;
                    String specialiteComplementairemedecin;

                    idmedecin = rs2.getString("id");
                    nommedecin = rs2.getString("nom");
                    prenommedecin = rs2.getString("prenom");
                    adressemedecin = rs2.getString("adresse");
                    telmedecin = rs2.getString("tel");
                    specialiteComplementairemedecin = rs2.getString("specialitecomplementaire");

                    Med m = new Med(nommedecin, prenommedecin, adressemedecin, telmedecin, specialiteComplementairemedecin, idmedecin);
                    lesMeds.add(m);

                }
                Dep d = new Dep(rs.getString("departement"), lesMeds);
                lesDeps.add(d);
            }
            return lesDeps;
        } catch (SQLException e) {
            System.out.println("Erreur SQL :" + e);
        }
        return null;
    }

    public static Collection<Spe> getLesSpe() {
        Collection<Spe> lesSpe = new HashSet<Spe>();
        try {
            Connection con = Connect.get();
            Statement req;
            req = con.createStatement();

            //1e partie
            ResultSet rs = req.executeQuery("select distinct departement from medecin order by departement ASC;");
            while (rs.next()) {
                if (rs.getString("specialitecomplementaire") != null) {
                    Collection<Med> lesMeds = new TreeSet<Med>();
                    Statement req2;
                    req2 = con.createStatement();
                    ResultSet rs2 = req2.executeQuery("select * from medecin where specialitecomplemantaire='" + rs.getString("specialitecomplemantaire") + "'");
                    while (rs2.next()) {

                        String idmedecin;
                        String nommedecin;
                        String prenommedecin;
                        String adressemedecin;
                        String telmedecin;
                        String specialiteComplementairemedecin;

                        idmedecin = rs2.getString("id");
                        nommedecin = rs2.getString("nom");
                        prenommedecin = rs2.getString("prenom");
                        adressemedecin = rs2.getString("adresse");
                        telmedecin = rs2.getString("tel");
                        specialiteComplementairemedecin = rs2.getString("specialitecomplementaire");

                        Med m = new Med(nommedecin, prenommedecin, adressemedecin, telmedecin, specialiteComplementairemedecin, idmedecin);
                        lesMeds.add(m);

                    }
                    Spe s = new Spe(rs.getString("specialitecomplementaire"), lesMeds);
                    lesSpe.add(s);
                }
            }
            return lesSpe;
        } catch (SQLException e) {
            System.out.println("Erreur SQL :" + e);
        }
        return null;

    }
}
